import pandas as pd
from sklearn.preprocessing import MinMaxScaler
import pymysql


import pymysql.cursors


class MysqlUtils(object):
    def __init__(self):
        self.conn= pymysql.connect(
            host='localhost',
            user="root",
            passwd="MYSQL123",
            db="scenic",
            port=3306,
            charset="utf8"
        )
    def is_holiday(self,date):
        '''
        是否节假日判断
        '''
        if date in ['2024-09-03','2024-10-01','2024-10-02','2024-10-03','2024-10-04',
                    '2024-10-05','2024-10-06','2024-10-07','2025-01-01','2025-01-02','2025-01-03']:
            return 1
        return 0
            
    def get_scenic_data(self):
        """"
        获取数据
        """
        cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
        sql = '''
        SELECT DATE(g.create_time) as date, HOUR(g.create_time) as hour, count(*) as count 
        FROM order_user_gate_rel g WHERE HOUR(g.create_time)
        BETWEEN 6 and 23 and DATE(g.create_time) <'2025-01-01'  GROUP BY  date ,hour     '''
        
        cursor.execute(sql)
        ret = cursor.fetchall()
        df = pd.DataFrame(ret)
        print(df.head)
        #格式转换
        date_range = pd.date_range(start='2024-07-01',end='2025-03-02',freq='D')
        hours = range(6,24)
        full_index = pd.MultiIndex.from_product([date_range,hours],names=['date','hour'])
        #print(full_index)
        df_full = df.set_index(['date','hour']).reindex(full_index,fill_value=0).reset_index()
        #按天数组织数据，每行包含18个小时的检票次数
        df_pivot = df_full.pivot(index='date',columns='hour',values='count')
        df_pivot['dow'] = df_pivot.index.dayofweek #星期几（0-6）
        df_pivot['month'] = df_pivot.index.month #月份
        df_pivot['is_holiday'] = df_pivot.index.map(self.is_holiday)
        #对星期几和月份进行独热编码
        df_pivot = pd.get_dummies(df_pivot,columns=['dow','month'],dtype=int)
        #归一化小时检验票
        hours_columns = list(range(6,24))
        df_hours = df_pivot[hours_columns].copy()
        
        feature_columns = [col for col in df_pivot.columns if col not in hours_columns ]
        df_feature = df_pivot[feature_columns].copy()
        # df_feature_columns = df_pivot[feature_columns].copy()
        
        scaler = MinMaxScaler()
        scaler_hours = scaler.fit_transform(df_hours)
        from joblib import dump
        dump(scaler,'5/scaler.joblib')
        
        #将归一化后的数据转化为DataFrame
        df_hours_scaled = pd.DataFrame(scaler_hours,columns=hours_columns,index=df_hours.index)
        #合并
        df_pivot_clean = pd.concat([df_hours_scaled,df_feature],axis=1)
        print(df_pivot_clean)
        
        df_pivot_clean.to_csv('5/scenic_data.csv',index=False)





        
if __name__ == '__main__':
    mu = MysqlUtils()
    mu.get_scenic_data()